<?php

namespace App\Http\Controllers\Excel;

use Illuminate\Http\Request;

use App\Http\Requests;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;


class ExcelController extends Controller
{
    //
    public function index()
    {
        set_time_limit(360);
        //dd(__FILE__);
        $uploadfile = 'C:\Wnmp\html\attendance-end-o\public\20161222.xls';
        require_once dirname(__FILE__) . '/../../Classes/PHPExcel/IOFactory.php';
        $reader = \PHPExcel_IOFactory::createReader('Excel5');
        $PHPExcel = $reader->load($uploadfile);
        $objWorksheet = $PHPExcel->getAllSheets();
        $objWorksheet = $objWorksheet[0];
        $highestRow = $objWorksheet->getHighestRow(); // 取得总行数
        $highestColumn = $objWorksheet->getHighestColumn(); // 取得总列数
        $arr = array(0 => 'A', 1 => 'B', 2 => 'C', 3 => 'D', 4 => 'E', 5 => 'F', 6 => 'G', 8 => 'H', 9 => 'I', 10 => 'J', 11 => 'K', 12 => 'L', 13 => 'M', 14 => 'N', 15 => 'O', 16 => 'P', 17 => 'Q', 18 => 'R', 19 => 'S', 20 => 'T', 21 => 'U', 22 => 'V', 23 => 'W', 24 => 'X', 25 => 'Y', 26 => 'Z');
//echo $highestRow.$highestColumn;
// 一次读取一列
        $res = array();
        for ($row = 2; $row <= $highestRow; $row++) {
            for ($column = 0; $arr[$column] != 'G'; $column++) {
                $val = $objWorksheet->getCellByColumnAndRow($column, $row)->getValue();
                $res[$row - 2][$column] = $val;
            }
        }

        //dd($res);
//        $res = Redis::get('students');
//        $res = json_decode($res);

        //dd($res[0]);

        $prog = [];
        $prog['计算机'] = 9;
        $prog['软件工程'] = 10;
        $prog['网络工程'] = 11;
        $prog['信息工程'] = 12;
        $prog['物联网'] = 13;
        $prog['软件测试'] = 14;
        $prog['信息安全'] = 15;
        $prog['网络技术'] = 16;
        $prog['软件技术'] = 17;

        $pro = [];
        $i = 0;
        $department = [];
        $department['计科系'] = 1;
        $profession = [];
        $pattern = "/\d*/i";
        $pattern1 = "/\D*/i";
        foreach ($res as $key => $val)
        {
            if ($val[5] == '计科系')
            {
                $pro[$i]['studentId'] = $val[3];
                $pro[$i]['name'] = $val[0];
                $pro[$i]['grade'] = $val[1];
                $prof = $val[2];

                //$classId = substr($val[3], 0, 5);
                //dd($classId);

                if (is_object($prof))
                {
                    //var_dump($prof->richTextElements[0]->text);
                    //dd();
                    if (count($prof->richTextElements) > 1)
                    {
                        $prof = $prof->richTextElements[0]->getText() . $prof->richTextElements[1]->getText();
                    }
                    else{
                        $prof = $prof->richTextElements[0]->getText();
                    }
                    //$prof = $prof->richTextElements[0]->getText();
                    //echo $val[3] . $prof . '<br>';
                }
                $pro[$i]['class'] = $prof;
                $prof = preg_replace($pattern,'',$prof);
                $pro[$i]['professionId'] = $prog[$prof];
                $pro[$i]['departmentId'] = 1;
                $i++;
            }
        }
        //dd();
        //dd($pro);
//       Redis::set('students', json_encode($pro));
//        $pro = Redis::get('students');
//        $pro = json_decode($pro);
//        $stu = DB::table('students');
//        foreach ($pro as $p)
//        {
//            $p = (array)$p;
//            $stu->insert($p);
//            //dd($p);
//        }
//        //Redis::set('profession', json_encode($profession));
//        //dd($pro);
        $stu = DB::table('students');
        $cla = DB::table('class');

        //$stu->insert($pro);
        //dd();
        $classes = [];

        foreach ($pro as $p)
        {
            //echo $p['class'] . '<br>';

            if (!in_array($p['class'], $classes))
            {
                $classes[] = $p['class'];
            }

            //$stu->insert($p);
            //$cla->where('class', '=', $p['class'])->increment('number');
            //$cla = DB::table('class');
        }

        $classNumber = [];

        foreach ($pro as $p)
        {
            try{
                if ( isset($classNumber[$p['class']]))
                {
                    $classNumber[$p['class']]++;
                }
                else{
                    $classNumber[$p['class']] = 1;
                }
            }
            catch (\Exception $exception)
            {
                dd($p['class']);
            }
        }

        //dd($classNumber);

        $classDB = [];
        $i = 0;
        foreach ($classes as $item)
        {
            $classDB[$i]['class'] = $item;

            $classDB[$i]['professionId'] = $prog[preg_replace($pattern, '', $item)];

            $classDB[$i]['departmentId'] = 1;

            $classDB[$i]['number'] = $classNumber[$item];

            $i++;
        }
        $cla->insert($classDB);
        dd($classDB);

        $classN = [];

        foreach ($classes as $c)
        {
            if ( !in_array($c, $classN))
            {
                $classN[$c] = 1;
            }
            else{
                $classN[$c] += 1;
            }
        }


        dd($classN);
    }

    public function index2()
    {
        set_time_limit(180);
        //dd(__FILE__);
        $uploadfile = 'C:\Wnmp\html\attendance-end-o\public\20161222.xls';
        require_once dirname(__FILE__) . '/../../Classes/PHPExcel/IOFactory.php';
        $reader = \PHPExcel_IOFactory::createReader('Excel5');
        $PHPExcel = $reader->load($uploadfile);
        $objWorksheet = $PHPExcel->getActiveSheet();
        $highestRow = $objWorksheet->getHighestRow(); // 取得总行数
        $highestColumn = $objWorksheet->getHighestColumn(); // 取得总列数
        $arr = array(0 => 'A',
            1 => 'B',
            2 => 'C',
            3 => 'D',
            4 => 'E',
            5 => 'F',
            6 => 'G',
            7 => 'H',
            8 => 'I',
            9 => 'J',
            10 => 'K',
            11 => 'L',
            12 => 'M',
            13 => 'N',
            14 => 'O',
            15 => 'P',
            16 => 'Q',
            17 => 'R',
            18 => 'S',
            19 => 'T',
            20 => 'U',
            21 => 'V',
            22 => 'W',
            23 => 'X',
            24 => 'Y',
            25 => 'Z');
//echo $highestRow.$highestColumn;
// 一次读取一列
        $res = array();
        for ($row = 2; $row <= 288; $row++) {
            for ($column = 10; $arr[$column] != 'M'; $column++) {
                $val = $objWorksheet->getCellByColumnAndRow($column, $row)->getValue();
                $res[$row - 2][$column] = $val;
            }
        }

        $prog = [];
        $prog['计算机'] = 9;
        $prog['软件工程'] = 10;
        $prog['网络工程'] = 11;
        $prog['信息工程'] = 12;
        $prog['物联网'] = 13;
        $prog['软件测试'] = 14;
        $prog['信息安全'] = 15;
        $prog['网络技术'] = 16;
        $prog['软件技术'] = 17;

        $progs = array('计算机',
            '软件工程',
            '网络工程',
            '信息工程',
            '物联网',
            '软件测试',
            '信息安全',
            '网络技术',
            '软件技术');



        $class = [];
        $i = 0;
        $table = DB::table('class');
        $pattern = "/\d+/i";
        $pattern1 = "/\D+/i";
        foreach ($res as $r)
        {

            $classA = $r[11];
            $classId = preg_replace($pattern1, '', $classA);
            $className = preg_replace($pattern, '', $classA);
            echo $className;
            if ( in_array($className, $progs) )
            {
                $class[$i]['class'] = $classA;
                $class[$i]['number'] = 0;
                $class[$i]['professionId'] = $prog[$className];
                $class[$i]['departmentId'] = 1;
                $i++;
            }

        }

        foreach ($class as $c)
        {
            $table->insert($c);
        }

        dd($class);
    }

    private function getNumber($db,$classId)
    {
        $number = $db->where('class', '=', $classId)->count();
        return $number;
    }
}
